﻿using LY.DataAccess.Repository;
using LY.Entity;
using LY.Entity.Entities;
using LY.Entity.PageSearch;
using LY.Entity.SearchEntity;
using System;
using System.Collections;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Text;

namespace LY.DataAccess.Repository
{
    public class MenuRepository : BaseRepository
    {
        public PagedList<Menu_EX> QueryMenuList(MenuSearch search, PageView view)
        {
            string cols = @" s1.[ID]
                          ,s1.[Name]
                          ,s2.[Name] AS UpperName
                          ,s1.[UpperID]
                          ,s1.[Code]
                          ,s1.[Path]
                          ,s1.[Sequence]
                          ,s1.[URL]
                          ,s1.[Status]
                          ,s1.[NameE]
                          ,s1.[Target] ";
            string sqlWhere = " AND s1.[UpperID] != 0 ";
            if (!string.IsNullOrEmpty(search.Name))
            {
                sqlWhere += " AND s1.[Name] LIKE '%" + search.Name + "%' ";
            }

            return base.PageGet<Menu_EX>(view, cols,
                @" [S_Menu] s1 LEFT JOIN [S_Menu] s2 ON s1.[UpperID]=s2.[ID] "
                , sqlWhere
                , "  s1.[ID] ASC ",
                "");
        }

        public List<C_Menu> GetTopMenuList(int? id)
        {
            string sql = @" SELECT [ID]
                          ,[Name]
                          ,[UpperID]
                          ,[Code]
                          ,[Path]
                          ,[Sequence]
                          ,[URL]
                          ,[Status]
                          ,[NameE]
                          ,[Target]
                       FROM [S_Menu] WHERE ID=@ID";
            return base.Query<C_Menu>(sql, new { ID = id });
        }

        public string GetCode(string code)
        {
            string sql = @" SELECT [Code] 
                       FROM [S_Menu] WHERE [Code]=@code";
            return base.Get<string>(sql, new { code = code });
        }

        public C_Menu GetMenu(int? id)
        {
            string sql = @" SELECT [ID]
                          ,[Name]
                          ,[UpperID]
                          ,[Code]
                          ,[Path]
                          ,[Sequence]
                          ,[URL]
                          ,[Status]
                          ,[NameE]
                          ,[Target]
                        ,[Layer]
                       FROM [C_Menu] WHERE ID=@ID";
            return base.Get<C_Menu>(sql, new { ID = id });
        }

        public int DeleteMenu(int id)
        {
            string sql = " DELETE FROM [C_Menu] WHERE ID=@ID";
            return base.Excute(sql, new { ID = id });
        }

        public List<C_Menu> GetUpperMenuList(int? id)
        {
            string sql = @" SELECT [ID]
                          ,[Name]
                          ,[UpperID]
                          ,[Code]
                          ,[Path]
                          ,[Sequence]
                          ,[URL]
                          ,[Status]
                          ,[NameE]
                          ,[Target]
                       FROM [C_Menu] WHERE [UpperID]=@ID";
            return base.Query<C_Menu>(sql, new { ID = id });
        }

        public List<C_Menu> GetUpperMenuListByMainId(int? id)
        {
            string sql = @" SELECT [ID]
                          ,[Name]
                          ,[UpperID]
                          ,[Code]
                          ,[Path]
                          ,[Sequence]
                          ,[URL]
                          ,[Status]
                          ,[NameE]
                          ,[Target]
                       FROM [C_Menu] WHERE [UpperID]=@ID order by path ";
            return base.Query<C_Menu>(sql, new { ID = id });
        }

        public List<C_Menu> GetSubMenuId(int? id)
        {
            string sql = @" SELECT top 1 [ID]
                       FROM [C_Menu] WHERE [UpperID]=@ID";
            return base.Query<C_Menu>(sql, new { ID = id });
        }

        public List<C_Menu> GetMenuList(int? UpperID)
        {
            string sql = @" SELECT [ID]
                          ,[Name]
                          ,[UpperID]
                          ,[Code]
                          ,[Path]
                          ,[Sequence]
                          ,[URL]
                          ,[Status]
                          ,[NameE]
                          ,[Target]
                       FROM [S_Menu] WHERE [ID]=@UpperID";
            return base.Query<C_Menu>(sql, new { UpperID = UpperID });
        }

        public DataView GetMenuView(int? id)
        {
            string sql = @" SELECT [ID]
                          ,[Name]
                          ,[UpperID]
                          ,[Code]
                          ,[Path]
                          ,[Sequence]
                          ,[URL]
                          ,[Status]
                          ,[NameE]
                          ,[Target]
                       FROM [S_Menu] WHERE UpperID=@ID";
            return base.Get<DataView>(sql, new { ID = id });
        }

        /// <summary>
        /// 根据栏目ID查询子栏目信息，返回数据集合
        /// </summary>
        /// <param name="search"></param>
        /// <param name="view"></param>
        /// <returns></returns>
        public PagedList<C_Menu> QueryMenuListId(int? Id, ArticleSearch search, PageView view)
        {
            string cols = @" [ID]
                          ,[Name]
                          ,[UpperID]
                          ,[UpperName]
                          ,[Code]
                          ,[Path]
                          ,[Sequence]
                          ,[URL]
                          ,[Status]
                          ,[NameE]
                          ,[Target]
                            ,Layer";

            string sqlWhere = "";
            if (Id.HasValue)
            {
                sqlWhere += "AND [UpperID] =" + Id + "";
            }
            if (!string.IsNullOrEmpty(search.Name))
            {
                sqlWhere += "AND [Name] LIKE '%" + search.Name + "%'";
            }
            return base.PageGet<C_Menu>(view, cols, @" [C_Menu]", sqlWhere, " [Sequence]", "");
        }

        /// <summary>
        /// 根据相应条件查询栏目信息，返回数据集合
        /// </summary>
        /// <param name="search"></param>
        /// <param name="view"></param>
        /// <returns></returns>
        public PagedList<C_Menu> QueryMenuList(ArticleSearch search, PageView view)
        {
            string cols = @" [ID]
                          ,[Name]
                          ,[UpperID]
                          ,[UpperName]
                          ,[Code]
                          ,[Path]
                          ,[Sequence]
                          ,[URL]
                          ,[Status]
                          ,[NameE]
                          ,[Target]
                            ,Layer";
            string sqlWhere = "";
            if (!string.IsNullOrEmpty(search.Name))
            {
                sqlWhere += "AND [Name] LIKE '%" + search.Name + "%' ";
            }
            sqlWhere += "AND id <> 170 ";

            //return base.PageGet<C_Menu>(view, cols, @" [C_Menu]", sqlWhere, " [Sequence]", "");
            return base.PageGet<C_Menu>(view, cols, @" [C_Menu]", sqlWhere, " [Path]", "");
        }

        /// <summary>
        /// 检索模块信息
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public List<C_Menu> GetMenuSub(int? id)
        {
            string sql = @" SELECT ID
                       FROM [C_Menu] WHERE Path like (SELECT Path+'%'
                       FROM [C_Menu] WHERE Id=@ID)";
            return base.Query<C_Menu>(sql, new { ID = id });

        }

        /// <summary>
        /// 检索模块信息
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public C_Menu GetMenuByMainMenuId(int? id)
        {
            string sql = @" SELECT top 1 ID
                       FROM [C_Menu] WHERE UpperId=@ID order by path ";
            return base.Get<C_Menu>(sql, new { ID = id });

        }

        //检查Code是否重复
        public int GetCodeNum(string code)
        {
            string sql = @" select COUNT(1) from C_Menu where Code=@code";
            return base.Get<int>(sql, new { code = code });
        }

        //修改时检查Code是否重复
        public int GetCodeNum(int id, string code)
        {
            string sql = @" select count(1) from (select * from C_Menu where code not in(select Code from C_Menu where ID=@id))r where Code=@code";
            return base.Get<int>(sql, new { id = id, code = code });
        }

        //根据栏目ID检查当前栏目下是否有文章信息
        public int CheckArticle(int menuid)
        {
            string sql = @" select COUNT(1) from C_Article where MenuId=" + menuid + "";
            return base.Get<int>(sql, new { menuid = menuid });
        }


        /// <summary>
        /// 更新栏目信息
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public int UpdateSequence(string oldpath, string newpath)
        {
            string sql = "  Update [C_Menu] set path = REPLACE(path,@oldpath,@newpath) WHERE Path like @oldpath + '%'  ";
            return base.Excute(sql, new { oldpath = oldpath, newpath = newpath });
        }

        /// <summary>
        /// 根据当前菜单的Id获取母菜单，目前只适用于2级菜单。
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public C_Menu SelectUpperMenuById(int id)
        {
            string sql = "select * from C_Menu where ID=(select UpperID from C_Menu where ID=@ID)";
            return base.Get<C_Menu>(sql, new { ID = id });
        }
    }
}
